﻿using System.Threading;
using System;
using System.Data;
using System.Data.Common;

using SimpleService.Database;

namespace SimpleService.ORM
{
    public class PersistenceObject
    {

       public DbConnection _connection; 
        public string _TableName = ""; 
        public string MaxKey = "";

        public int RowsCount = 0;
        public int ColsCount = 0;
        public string[] ColsName;
        public string[] ColsType;

        public Rows Rows;
        public Select Select;
        public Insert Insert;
        public Delete Delete;

        public DataTable datatable;


        private PersistenceObject() { }

        private PersistenceObject(DbConnection connection, string tableName)
        {
            _connection = connection;
            _TableName = tableName;

            Rows = new Rows(this);
            Select = new Select(this);
            Insert = new Insert(this);
            Delete = new Delete(this);

        }

        public static bool delete(DbConnection connection, string tableName, string where)
        {
            PersistenceObject Table = new PersistenceObject(connection, tableName);
            Table.datatable = Table.dt();
            return Table.Delete.Execution(where);
           
        }

        public static PersistenceObject select(DbConnection connection, string tableName)
        {
            PersistenceObject Table = new PersistenceObject(connection, tableName);
            Table.Select.Execution();
            return Table;
        }

        public static PersistenceObject select(DbConnection connection, string tableName, string where)
        {
            PersistenceObject Table = new PersistenceObject(connection, tableName);
            Table.Select.Execution(where);
            return Table;
        }

        public static PersistenceObject insert(DbConnection connection, string tableName)
        {
            PersistenceObject Table = new PersistenceObject(connection, tableName);

            Table.datatable = Table.dt();

            Table.Insert.Execution();
            Table.MaxKey = Table.getMaxkey();
            Table.Select.Execution(Table.MaxKey);
            return Table;
        }

        public static PersistenceObject insert(DbConnection connection, string tableName, string key)
        {
            PersistenceObject Table = new PersistenceObject(connection, tableName);
            Table.datatable = Table.dt();
            Table.RowsCount = PersistenceUtility.RowsCount(Table.datatable); 
            Table.ColsCount = PersistenceUtility.ColumnsCount(Table.datatable); 
            Table.ColsName = PersistenceUtility.ColumnsName(Table.datatable); 
            Table.ColsType = PersistenceUtility.ColumnsType(Table.datatable); 	
            DataTable dt = Table.dt(key);


            if (dt.Rows.Count > 0)
            {
                Table.Select.Execution(key);
            }
            else
            {
                Table.Insert.Execution(key);
                Table.Select.Execution(key);
            }
            return Table;
        }

        public string getMaxkey()
        {
            string SQLstr = ORMConfig.maxkey;
            SQLstr = SQLstr.Replace("__table__", _TableName);
            SQLstr = SQLstr.Replace("__key__", ColsName[0]); 

            return SqlOperator.RunValue(SQLstr, _connection);
        }

        public bool delete(string key)
        {
         

          //  string SQLstr = ORMConfig.delete;
           // SQLstr = SQLstr.Replace("__table__", _TableName);
            //SQLstr = SQLstr.Replace("__key__", ColsName[0]); 
            //SQLstr = SQLstr.Replace("__val__", KeyType(key));

            PersistenceObject Table = new PersistenceObject(_connection, _TableName);
            Table.datatable = Table.dt();
            return Table.Delete.Execution(key);

            //return SqlOperator.ExecuteNonQuery(SQLstr, _connection);	
        }

        public string KeyType(string keying)
        {
            string ss = ColsType[0];
            string ans = "";
            if (ss == "System.Int32")
            {
                ans = keying;
            }
            else
            {
                ans = "'" + keying + "'";
            }
            return ans;
        }

     
        public DataTable dt()
        {
            string SQLstr = ORMConfig.select;
            SQLstr = SQLstr.Replace("__table__", _TableName);

            DataTable dt = SqlOperator.Table(SQLstr, _connection);

            datatable = dt;

            return dt;
        }

        public DataTable dt(string key)
        {
            string SQLstr = "";
            try
            {
                if (key.ToLower().IndexOf("where ") > -1)
                {
                    SQLstr = ORMConfig.select  + key;
                    SQLstr = SQLstr.Replace("__table__", _TableName);
                }
                else
                {
                    SQLstr = ORMConfig.select + " " + ORMConfig.where;
                    SQLstr = SQLstr.Replace("__table__", _TableName);
                    SQLstr = SQLstr.Replace("__key__", "" + ColsName[0]); 
                    SQLstr = SQLstr.Replace("__val__", "" + KeyType(key));
                }

                DataTable dt = SqlOperator.Table(SQLstr, _connection);

                datatable = dt;

                return dt;
            }
            catch (Exception e) { throw new Exception("sql<hr/>" + SQLstr + "<hr/>" + e.Message); }

        }



        private void begin()
        {
            try
            {
                string SQLstr = " BEGIN; ";
                SqlOperator.ExecuteNonQuery(SQLstr, _connection);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }
        private void commit()
        {
            try
            {
                string SQLstr = " COMMIT; ";
                SqlOperator.ExecuteNonQuery(SQLstr, _connection);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

    }
}
